

*****************************************************************
*****************************************************************
*** 0. General settings
*****************************************************************
*****************************************************************

* clear
clear
clear 		mata
mat 		drop _all

* set path
global		path	""							// FS Max Macbook	

/*******************************************************************************
 
	TABLE OF CONTENTS														
	01 Descritive analysis - Figures
		a. 	Dealscan: Facility infos 
		b. 	Dealscan: Package infos (Issue Date, Purpose, Amount)
		c.  Dealscan: Borrower infos 		
		d.  Dealscan: Pricing Info	
		e.  Dealscan: Performance Pricing		
		f.  Dealscan: LeadArranger Info, if all lenders needed, comment out lead part	
		g.  Dealscan: Sublimits			
		h.  Dealscan: Bank infos
		i.  Dealscan: Collateral information		
		j.  Dealscan: Covenant information		

******************************************************************************/




/*******************************************************************************
 												
	01  Read various innput files
		a. Dealscan: Facility infos 	
	
*******************************************************************************/

		* Load data
		**************
		use "$path/0 Data/Dealscan/July 2021/facility.dta", clear /*Unique Key = FacilityID*/
		
		* rename lower case letters
		rename *, lower
		
		keep 	facilityid packageid facilitystartdate facilityenddate loantype maturity facilityamt primarypurpose seniority countryofsyndication secured currency exchangerate
		gen 	secured_d = (secured == "Yes") /*Missing Value --> Secured_d == 0*/
		gen 	secured_d2 = (secured == "Yes") if secured != "" /*Missing Value --> Secured_d2 == .*/
		drop 	secured
		
		
		* Primary Purposes
		********************
		
		gen 		primarypurpose2 = primarypurpose
		#d ;
		replace		primarypurpose2 = "Other" if inlist(primarypurpose, "Acquis. line", "Corp. purposes", "CP backup", "Debt Repay.") == 0 &
											inlist(primarypurpose, "Debtor-in-poss.", "LBO", "MBO", "Recap.", "Takeover", "Work. cap.") == 0;
		#d cr
		
		replace 	primarypurpose2 = "LBO/MBO" if inlist(primarypurpose2, "LBO", "MBO") 
		
		
		* Loan Type
		******************
		
		gen 	loantype2 = "TermLoan" if inlist(loantype, "Term Loan", "Term Loan A", "Term Loan B", "Term Loan C", "Term Loan D", "Term Loan E", "Term Loan F", "Term Loan F", "Term Loan H")
		replace	loantype2 = "TermLoan" if inlist(loantype, "Delay Draw Term Loan", "Term Loan I", "Term Loan J", "Term Loan K") 
		replace	loantype2 = "Revolver" if inlist(loantype, "Revolver/Line < 1 Yr.", "Revolver/Line >= 1 Yr.", "364-Day Facility", "Limited Line")
		replace loantype2 = "Revolver" if inlist(loantype, "Revolver/Term Loan")
		replace	loantype2 = "Other" if missing(loantype2)
		
		
		* Seniority
		*********************
		
		gen senior = ( trim(seniority) == "Senior" )

		* Destring 
		*********************
		
		replace maturity = "." if maturity == "nan"
		destring facilityid packageid facilityamt exchangerate maturity, replace force
		
		
		* FacAmt in mn
		*********************
		
		replace 	facilityamt = facilityamt / 1000000
		sort 		facilityid
		
		* Format dates
		*********************
	
		generate 	year 		= substr(facilitystartdate,1,4)
		destring year, replace
		generate 	month 		= substr(facilitystartdate,6,2)
		destring month, replace
		generate 	day 		= substr(facilitystartdate,9,2)
		destring day, replace
		gen facilitystartdate2 = mdy(mo, da, ye)
		format facilitystartdate2 %td
		
		drop facilitystartdate year month day
		rename facilitystartdate2 facilitystartdate
		
		generate 	year 		= substr(facilityenddate,1,4)
		destring year, replace force
		generate 	month 		= substr(facilityenddate,6,2)
		destring month, replace
		generate 	day 		= substr(facilityenddate,9,2)
		destring day, replace
		gen facilityenddate2 = mdy(mo, da, ye)
		format facilityenddate2 %td
		
		drop facilityenddate year month day
		rename facilityenddate2 facilityenddate
		
		* save
		****************
		
		save 	"$path/0 Data/_Processed/temp/_Archive/_Facility", replace
	
	
	

/*******************************************************************************
 												
	01  Read various innput files
		b. Dealscan: Package infos (Issue Date, Purpose, Amount) 	
	
*******************************************************************************/

		* Load data
		**************
		use 	"$path/0 Data/Dealscan/July 2021/package.dta", clear 
		
		* rename lower case letters
		rename *, lower 
		
		keep 	borrowercompanyid packageid dealactivedate refinancingindicator dealpurpose dealamount dealstatus
		
		* Destring
		***************
		
		destring packageid, replace
		destring borrowercompanyid, replace
		destring(dealamount), replace force
		
		* Dates
		******************
		
		generate 	year 		= substr(dealactivedate,1,4)
		destring year, replace
		generate 	month 		= substr(dealactivedate,6,2)
		destring month, replace
		generate 	day 		= substr(dealactivedate,9,2)
		destring day, replace
		gen dealactivedate2 = mdy(mo, da, ye)
		format dealactivedate2 %td
		
		drop dealactivedate month day
		rename dealactivedate2 dealactivedate
		
		generate 	quarter 	= yq(year(dealactivedate), quarter(dealactivedate))
		format		quarter 	%tq
		generate	month 		= ym(year(dealactivedate), month(dealactivedate))
		format		month %tm
			
		sort 		borrowercompanyid 	dealactivedate
		by 			borrowercompanyid: 	gen dealnumber = _n
		sort		packageid
		
		* DealAmt in mn
		*********************
		
		replace 	dealamount = dealamount / 1000000
		sort 		packageid
		
		* save
		****************
					
		save 	"$path/0 Data/_Processed/temp/_Archive/_Package", replace	
	
	
	
/*******************************************************************************
 												
	01  Read various innput files
		c.  Dealscan: Borrower infos 	
	
*******************************************************************************/

		* Load data
		**************
		use "$path/0 Data/Dealscan/July 2021/company.dta", clear 
		/*Unique Key = BorrowerCompanyID*/
		
		* rename lower case letters
		****************************
		rename 		*, lower
		
		keep 		companyid company primarysiccode publicprivate region country parentid ultimateparentid sales ticker
		
		
		rename 		companyid 			borrowercompanyid
		rename 		company 			borrowername
		rename 		publicprivate 		borrowerpublicprivate
		rename		region 				borrowerregion
		rename		country 			borrowercountry
		rename		parentid 			borrowerparentid
		rename 		ultimateparentid	borrowerultimateparentid
		rename		sales 				borrower_sales
		rename		ticker 				borrower_ticker
		sort 		borrowercompanyid
		
		* save
		****************

		save "$path/0 Data/_Processed/temp/_Archive/_Comp", replace
	
	
	
/*******************************************************************************
 												
	01  Read various innput files
		d.  Dealscan: Pricing Info	
	
*******************************************************************************/

		* Load data
		**************	
		use "$path/0 Data/Dealscan/July 2021/currfacpricing.dta", clear 
		/*Unique Key = BorrowerCompanyID*/

		* rename lower case letters
		rename *, lower
		
		
		replace 	baserate 	= trim(baserate)
		replace 	fee 		= trim(fee)
		keep 		facilityid baserate fee minbps maxbps allindrawn allinundrawn
		rename		allindrawn 		AISD
		rename		allinundrawn 	AISU
		
		bys fee: 	egen tempN = count(facilityid)
		
		*Spread
		*****************
		
		generate	_LIBOR 		= maxbps if baserate == "LIBOR"
		bysort 		facilityid: egen LIBOR = max(_LIBOR)
		drop		_LIBOR
		label 		variable	LIBOR "LIBOR"
		
		*Facility regular fee / Annual regular fee
		********************************************
		
		gen			_AFR 			= maxbps	if fee == "Annual Regular Fee"
		bysort 		facilityid: 	egen AFR 	= max(_AFR)
		drop		_AFR
		label 		variable AFR "Facility regular fee"
		
		
		*Commitment regular fee
		********************************************
		
		gen			_CF 		= maxbps 		if fee == "Commitment Regular Fee"
		bysort 		facilityid: egen CF 		= max(_CF)
		drop		_CF
		label 		variable 	CF "Commitment regular fee"
		
		
		*Upfront regular fee
		********************************************
		
		gen			_UFR 		= maxbps 		if fee == "Upfront Regular Fee"
		bysort 		facilityid: egen UFR 		= max(_UFR)
		drop		_UFR
		label 		variable 	UFR "Upfront regular fee"
		
		*Standby LC fee
		********************************************
		
		gen			_LCF 		= maxbps 		if fee == "Standby LC"
		bysort 		facilityid: egen LCF 		= max(_LCF)
		drop		_LCF
		label 		variable 	LCF "Standby LC fee"
		
		
		*Utilization fee
		********************************************
		
		gen			_UTF = maxbps if fee == "Utilization Fee"
		bysort 		facilityid: egen UTF = max(_UTF)
		drop		_UTF
		label variable UTF "Utilization fee" 
		
		
		*Cancellation fee
		********************************************
		
		gen			_CAF 		= maxbps 		if fee == "Cancellation Fee"
		bysort 		facilityid: egen CAF 		= max(_CAF)
		drop		_CAF
		label 		variable 	CAF "Cancellation fee"
		
		
		*Trade LC fee
		********************************************
		
		gen			_TLF 		= maxbps 		if fee == "Trade LC"
		bysort 		facilityid: egen TLF 		= max(_TLF)
		drop		_TLF
		label 		variable 	TLF "Trade LC fee"
		
		
		*Term-out fee
		********************************************
		
		gen			_TOF 		= maxbps 		if fee == "Term-Out Fee"
		bysort 		facilityid: egen TOF 		= max(_TOF)
		drop		_TOF
		label 		variable	TOF "Term-out fee"
		
		
		*Extension fee
		********************************************
		
		gen			_EXF 		= maxbps 		if fee == "Extension Fee"
		bysort 		facilityid: egen EXF 		= max(_EXF)
		drop		_EXF
		label 		variable 	EXF "Extension fee"
		
		
		*Documentary LC fee*
		*******************************************
		
		gen			_DLF 		= maxbps 		if fee == "Documentary LC Fee"
		bysort 		facilityid: egen DLF 		= max(_DLF)
		drop		_DLF	
		label 		variable 	DLF 	"Documentary LC fee"
		
		
		*Collateral Monitoring fee
		********************************************
		
		gen			_CMF 		= maxbps 	if fee == "Collateral Monitoring Fee"
		bysort 		facilityid: egen CMF 	= max(_CMF)
		drop		_CMF	
		label 		variable 	CMF "Collateral monitoring fee"
		
		
		*Management fee 
		********************************************
		
		gen			_MGF 		= maxbps 	if fee == "Mgt & u/w fee"
		bysort 		facilityid: egen MGF	= max(_MGF)
		drop		_MGF	
		label 		variable 	MGF "Management fee" 
		
		
		*Combined fee
		********************************************
		
		gen			_CBF 		= maxbps 	if fee == "Combined fees"
		bysort 		facilityid: egen CBF 	= max(_CBF)
		drop		_CBF
		label 		variable 	CBF "Combined fee"
		
		
		*Selling concession
		********************************************
		
		gen			_SEC 		= maxbps 	if fee == "Selling concession"
		bysort 		facilityid: egen 		SEC = max(_SEC)
		drop		_SEC
		label 		variable 	SEC "Selling concession"
		
		
		*Additional fee
		********************************************
		
		gen			_ADF 		= maxbps 	if fee == "Additional fee"
		bysort 		facilityid: egen ADF 	= max(_ADF)
		drop		_ADF 
		label 		variable	ADF "Additional fee"
		
		
		*Put-option waiver fee
		********************************************
		
		gen			_POF = maxbps if fee == "Put-option waiver fee"
		bysort 		facilityid: egen POF = max(_POF)
		drop		_POF
		label 		variable POF "Put-option waiver fee"
		
		
		*Guarantee fee
		********************************************
		
		gen			_GUF = maxbps 	if fee == "Guarantee Fee"
		bysort 		facilityid:	 	egen GUF = max(_GUF)
		drop		_GUF
		label 		variable 		GUF "Guarantee fee"
		
		generate	baseratetype = 1 if baserate == "LIBOR" /*~116,000 entries*/
		replace		baseratetype = 2 if baserate == "Prime" /*~48,000 entries*/
		replace		baseratetype = 3 if baserate == "Fixed Rate" /*~20,000 entries*/
		replace		baseratetype = 4 if baserate == "Euribor" /*~11,000 entries*/
		replace		baseratetype = 5 if missing(baseratetype) /*"Exotic base rates with <10,000 entries worldwide"*/
		replace		baseratetype = 6 if missing(baserate) /*Missing base rates*/
		
		sort		facilityid baseratetype
		duplicates 	drop facilityid, force
		
		keep  		facilityid baserate AISD AISU LIBOR AFR CF UFR LCF UTF CAF TLF TOF EXF DLF CMF MGF CBF SEC ADF POF GUF minbps maxbps
					
		sort 		facilityid
		
		* save
		****************
		
		save 		"$path/0 Data/_Processed/temp/_Archive/_Pricing", replace
			
		
	
/*******************************************************************************
 												
	01  Read various innput files
		e.  Dealscan: Performance Pricing	
	
*******************************************************************************/

		* Load data
		**************
		use "$path/0 Data/Dealscan/July 2021/performancepricing.dta", clear
		
		
		* rename lower case letters
		rename 	*, lower
		
		sort 		facilityid
		generate 	PP_RAT 		= (rating_min != "" | rating_max != "")
		generate 	PP_BS 		= (ratio_min != "" | ratio_max != "")
		generate 	PP 			= PP_RAT + PP_BS
		keep 		facilityid PP_RAT PP_BS PP libor annualfee commitmentfee utilizationfee
		
		collapse (max) PP_RAT PP_BS PP (min) LIBORmin= libor AFRmin= annualfee CFmin= commitmentfee UTFmin= utilizationfee (max) LIBORmax= libor AFRmax= annualfee CFmax= commitmentfee UTFmax= utilizationfee, by(facilityid)
		sort 	facilityid
		
		* save
		****************

		save 	"$path/0 Data/_Processed/temp/_Archive/_PerformancePricing", replace	
		
		
		
/*******************************************************************************
 												
	01  Read various innput files
		f.  Dealscan: LeadArranger Info, if all lenders needed, comment out lead part	
	
*******************************************************************************/

		* Load data
		**************
		use "$path/0 Data/Dealscan/July 2021/lendershares.dta", clear 
	
	/*No unique key, (FacilityID, Lender, LenderRole) is unique key apart from 6 observations*/
 
		* rename lower case letters
		
		rename *, lower
		
		* sole lender facilities
		**************************
		
		bys 		facilityid: 	egen 	syndicatesize 	= count(facilityid) /*count=1 --> Sole Lender Transactions*/
		generate	solelender 		= 		(syndicatesize 	== 1)
		
		replace		lenderrole 		= trim(lenderrole) 
		generate	lead 			= (leadarrangercredit 	== "Yes")
		replace 	lead 			= 1 if inlist(lenderrole, "Agent", "Admin agent", "Arranger", "Lead bank")
		replace		lead			= 1 if solelender == 1
		
		bys			facilityid: 	egen leadsize 					= sum(lead==1)
		bys			facilityid: 	egen sumbankallocation 			= total(bankallocation)
		bys			facilityid: 	egen sumleadbankallocation 		= total(bankallocation * lead)
		
		/* Keep all lenders indepent of role in syndicate
		drop	 	if lead == 0
		*/
		sort 		facilityid
		
		/* Don't overwrite file
		save		"$path/0 Data/_Processed/temp/_Archive/_Lead0", replace
		*/
		
		save		"$path/0 Data/_Processed/temp/_Archive/_Lead0all", replace
		
		rename		companyid lcoid
		keep  		facilityid lender lcoid bankallocation syndicatesize solelender lead leadsize sumbankallocation sumleadbankallocation
			
		* save
		****************
		/* Don't overwrite file
		save 		"$path/0 Data/_Processed/temp/_Archive/_Lead", replace
		*/
		

		save 		"$path/0 Data/_Processed/temp/_Archive/_Leadall", replace	
				
		
	
	
/*******************************************************************************
 												
	01  Read various innput files
		g.  Dealscan: Sublimits	
	
*******************************************************************************/

		* Load data
		**************
		use "$path/0 Data/Dealscan/July 2021/sublimits.dta", clear
		
		* rename lower case letters
		rename *, lower
		
		keep  	facilityid swingline competitivebid letterofcredit
		replace swingline 		= swingline / 1000000
		rename	letterofcredit LOCAmt
		replace LOCAmt 			= LOCAmt / 1000000
		sort 	facilityid
		
		* save
		****************

		save 		"$path/0 Data/_Processed/temp/_Archive/_Sublimits", replace			
		
		
		
		
/*******************************************************************************
 												
	01  Read various innput files
		h.  Dealscan: Bank infos
	
*******************************************************************************/

		* Load data
		**************
		use "$path/0 Data/Dealscan/July 2021/company.dta", clear
		/*Unique Key = LenderCompanyID*/
		
		* rename lower case letters
		rename *, lower
		
		keep 		companyid 			company primarysiccode publicprivate region country institutiontype parentid ultimateparentid sales ticker
		ren 		companyid 			lcoid
		ren 		company 			lendername
		ren 		publicprivate 		lenderpublicprivate
		ren			region 				lenderregion
		ren			country 			lendercountry
		ren			institutiontype 	lenderinstitutiontype
		ren			parentid 			lenderparentid
		ren 		ultimateparentid 	lenderultimateparentid
		ren			sales  				lender_sales
		ren			ticker 				lender_ticker
		sort 		lcoid
 
 
 
		* save
		****************

		save 		"$path/0 Data/_Processed/temp/_Archive/_Lender", replace					
		
		
		
/*******************************************************************************
 												
	01  Read various innput files
		I.  Dealscan: Collateral information
	
*******************************************************************************/

		* Load data
		**************
		use "$path/0 Data/Dealscan/July 2021/facilitysecurity.dta", clear

		
		* rename lower case letters
		*****************************
		rename *, lower
		
		drop 		comment index
		bysort 		facilityid: g n	=_n
		bysort 		facilityid: g securednum=_N
		generate	secured_d3		=1
		reshape 	wide security, i(facilityid) j(n)
		sort 		facilityid	
		
		* save
		****************

		save 		"$path/0 Data/_Processed/temp/_Archive/_Security", replace			
		
		
		
/*******************************************************************************
 												
	01  Read various innput files
		j.  Dealscan: Covenant information
	
*******************************************************************************/

		* Load data
		**************
		use "$path/0 Data/Dealscan/July 2021/financialcovenant.dta", clear
		
		* rename lower case letters
		rename *, lower
		
		
	    * Capital- and Performance-based Covenants based on Christensen and Nikolaev (2012), and Freudenberg et al (2015)
		*****************************************************************************************************************************
		
	    generate 			covenant				=	1
	    bys packageid: 		generate covenantnum	=	_N
	    bys packageid: 		generate n				=	_n
	    
	    * capital-based covenants
		**************************************
		
	    g capitalcovenant = (covenanttype=="Min. Quick Ratio" | covenanttype=="Min. Current Ratio"  | covenanttype=="Max. Debt to Equity"  | covenanttype=="Max. Loan to Value" | covenanttype=="Max. Debt to Tangible Net Worth"  | covenanttype=="Net Worth Max. Leverage ratio")
	    
		
		
	    * profitability-based covenants
		**************************************
		
	    g profcovenant = (covenanttype=="Min. Cash Interest Coverage" | covenanttype=="Min. Debt Service Coverage" | covenanttype=="Min. EBITDA" | covenanttype=="Min. Fixed Charge Coverage" ///
	                                     | covenanttype=="Min. Interest Coverage"   | covenanttype=="Max. Senior Debt to EBITDA" | covenanttype=="Max. Debt to EBITDA")
	   
	    * five broad categories of covenants: coverage, leverage, liquidity, net worth, capital expenditures
		*****************************************************************************************************************************
		
		g coveragecovenant = (covenanttype=="Min. Interest Coverage"  | covenanttype=="Min. Fixed Charge Coverage" | covenanttype=="Max. Debt to EBITDA"  | covenanttype=="Max. Senior Debt to EBITDA" /// 
							| covenanttype=="Min. Debt Service Coverage" | covenanttype=="Min. Cash Interest Coverage")
	    g liquiditycovenant = (covenanttype=="Min. Quick Ratio" | covenanttype=="Min. Current Ratio")
	    g leveragecovenant = (covenanttype=="Max. Leverage ratio" | covenanttype=="Max. Senior Leverage"  | covenanttype=="Max. Debt to Equity" | covenanttype=="Max. Debt to Tangible Net Worth" | covenanttype=="Max. Loan to Value")
	    g networthcovenant = (covenanttype=="Max. Long-Term Investment to Net Worth" | covenanttype=="Min. Net Worth to Total Asset")
		g capexcovenant = covenanttype=="Max. Capex"
		g othercovenant = (liquiditycovenant ==0  & leveragecovenant == 0 & networthcovenant == 0 & capexcovenant == 0)
		
		keep packageid n covenantnum covenanttype covenant covenantnum capitalcovenant profcovenant coveragecovenant liquiditycovenant leveragecovenant networthcovenant capexcovenant othercovenant
		
		local covenants capitalcovenant profcovenant coveragecovenant liquiditycovenant leveragecovenant networthcovenant capexcovenant othercovenant
		
		foreach var of local covenants{
		
			bys packageid: egen max`var'=max(`var')
			drop `var'
			}
			
		ren maxcapitalcovenant 		capitalcovenant 
		ren maxprofcovenant			profcovenant
		ren maxcoveragecovenant 	coveragecovenant
		ren maxliquiditycovenant 	liquiditycovenant
		ren maxleveragecovenant 	leveragecovenant 
		ren maxnetworthcovenant 	networthcovenant 
		ren maxcapexcovenant 		capexcovenant
		ren maxothercovenant 		othercovenant
		
		reshape wide covenanttype, i(packageid) j(n)
		
		* Destring
		****************
		destring packageid, replace
		
		* save
		****************

		save 		"$path/0 Data/_Processed/temp/_Archive/_Covenants", replace				

/*******************************************************************************
 												
	02  Merge data sets
		 	
	
*******************************************************************************/		
		
		
	
		* Facility
		************************************
		clear
		use "$path/0 Data/_Processed/temp/_Archive/_Facility"
		

		
		* Merge with all lender information
		************************************
		merge 1:m facilityid using "$path/0 Data/_Processed/temp/_Archive/_Leadall"
		/* 
		Result                           # of obs.
		-----------------------------------------
		not matched                         5,168
			from master                     5,168  (_merge==1)
			from using                          0  (_merge==2)

		matched                         2,150,142  (_merge==3)
		-----------------------------------------
		Non-merged facilities are non-US facilities 
		*/
		rename	_merge _mergelender		
	

	
	* Merge with Package infos
		************************************
		
		merge n:m	packageid using "$path/0 Data/_Processed/temp/_Archive/_Package"
		keep 	if _merge == 3 /*ONLY FACILITIES WITH AVAILABLE PACK-INFO KEPT IN DATASET (<0.01% deleted)*/
		drop 	_merge
	
		* Merge with Borrower infos 
		************************************
		
		merge 		n:1 	borrowercompanyid using "$path/0 Data/_Processed/temp/_Archive/_Comp"
		drop		if 		_merge == 2
		drop 		_merge
		
			
		*Merge with Borrower Ultimate Parent infos   (merge via ultimateparentid)
		***********************************************************************
		
		rename 		borrowerultimateparentid companyid
		sort 		companyid
		merge 		m:1 	companyid 	using "$path/0 Data/Dealscan/July 2021/company.dta" //"$path/0 Data/_Processed/temp/company"
		drop		if 		_merge == 2
		drop 		_merge
		
		
		drop 		ultimateparentid	/*This is the ultimateparent of the ultimateparent, in <0.01% of cases this is not equal to the ultimateparent itself*/
		drop 		parentid sales ticker publicprivate city state  zipcode  institutiontype secondarysiccode tertiarysiccode			
		rename 		companyid borrowerultimateparentid
		rename 		company borrowerultimateparentcompany
		rename 		region borrowerultimateparentregion
		rename 		country borrowerultimateparentcountry
		
		
		* Merge with Pricing infos
		************************************
		
		sort 	facilityid
		merge 	m:1 facilityid using "$path/0 Data/_Processed/temp/_Archive/_Pricing"
		drop 	if _merge == 2
		drop 	_merge
						
				
				
		* e) Merge with Performance pricing infos 
		**********************************************
		
		sort 	facilityid
		merge 	m:1 facilityid using "$path/0 Data/_Processed/temp/_Archive/_PerformancePricing"
		drop 	if _merge == 2
		drop 	_merge
		
		
/*		
		* Merge with Lead Bank 
		************************************
		
		merge	1:n facilityid using "$path/0 Data/_Processed/temp/_Archive/_Lead"
		drop	if _merge == 2
		rename	_merge _mergelender 		// there are facilities with more than 1 lead bank

		
*/

		
		* Merge with sublimits
		************************************
		
		merge 	n:1 facilityid using "$path/0 Data/_Processed/temp/_Archive/_Sublimits"
		drop	if _merge == 2
		drop	_merge
				
		
		* Merge with lender information
		************************************
				
		merge 	n:1 lcoid using "$path/0 Data/_Processed/temp/_Archive/_Lender"
		drop 	if _merge == 2 
		drop 	_merge
		
	
		* Lead arranger ultimate parent information (merge via leadarrangerultimateparentid)*
		*************************************************************************************************
		
		rename 	lenderultimateparentid 	companyid
		sort	companyid
		merge	m:1 companyid using "$path/0 Data/Dealscan/July 2021/company.dta" //"$path/0 Data/_Processed/temp/company"
		drop 	if _merge == 2 
		drop 	_merge
		
		drop 	ultimateparentid	/*This is the ultimateparent of the ultimateparent, in <0.01% of cases this is not equal to the ultimateparent itself*/
		drop	parentid sales ticker publicprivate city state  zipcode  institutiontype secondarysiccode tertiarysiccode			
		rename 	companyid LAultimateparentid
		rename 	company LAultimateparentcompany
		rename 	region LAultimateparentregion
		rename 	country LAultimateparentcountry
	
		
		
		* k) Merge with security info
		************************************
		
		merge n:1 facilityid using "$path/0 Data/_Processed/temp/_Archive/_Security"	
		g secured_dd=secured_d
		replace secured_dd=1 if secured_d3==1
		drop secured_d3
		ren secured_dd secured_d3
		drop _merge
		
	  	/* l) Dealscan: Covenant information*/
		*******************************************
		
	  	merge n:1 packageid using "$path/0 Data/_Processed/temp/_Archive/_Covenants"
	  	drop if _merge == 2
	  	drop _merge
	  	local covs covenant covenantnum capitalcovenant coveragecovenant liquiditycovenant leveragecovenant networthcovenant capexcovenant othercovenant
		foreach var of local covs{
		replace `var'=0 if `var'==.
		}
		
		
		* Only US borrowers
		****************************
		
		keep if borrowercountry			==		"USA"
		keep if currency				==		"United States Dollars"
		keep if countryofsyndication 	==	 	"USA"
		
		* save
		*******************
		
		save  "$path/0 Data/_Processed/Master_beforecompu_July2021", replace	
		
		
		
		
		**** prepare exposure data set
		
		use "$path/0 Data/_Processed/Master_beforecompu_July2021", clear
		
		gen year_of_maturity = year(facilityenddate)
		keep if year_of_maturity > 2018
		gen dealactivedate_y = year(facilitystartdate)
		gen dealactivedate_q = quarter(facilitystartdate)
		gen dealactivedate_y_q = (dealactivedate_y-1960)*4 + dealactivedate_q -1
		format dealactivedate_y_q %tq
		
		gen bank_lending_amount = facilityamt
		
		gen term_loan = inlist(loantype, "Term Loan", "Term Loan A", "Term Loan B", "Term Loan C", "Term Loan D", "Term Loan E", "Other Loan", "Bridge Loan")
	
		gen credit_line = inlist(loantype,"Revolver/Line >= 1 Yr.", " Revolver/Line < 1 Yr.", "Standby Letter of Credit", "364-Day Facility")
	
		replace credit_line = 1 if inlist(loantype, "Term Loan A", "Delay Draw Term Loan")
	
		
		
		*divide outstanding amount by number of banks in syndicate
		bysort facilityid: gen N_lenders = _N
		replace bank_lending_amount = bank_lending_amount/N_lenders

		
		sort gvkey borrowercompanyid term_loan dealactivedate_y dealactivedate_q
		
		save "$path/0 Data/_Processed/temp/_Archive/deals_aux_file.dta", replace
		use "$path/0 Data/_Processed/temp/_Archive/deals_aux_file.dta", clear
		
		
		local year_min = 2019
		local year_max = 2020
		
		
		keep gvkey borrowercompanyid term_loan
		duplicates drop
		
		
		local num_years = (`year_max'-`year_min'+1)
		expand 4*`num_years'
		
		
		bysort gvkey borrowercompanyid term_loan: gen exposuredate_y = floor(`year_min'+(_n-1)/4)
		bysort gvkey borrowercompanyid term_loan exposuredate_y: gen exposuredate_q = _n
		
		sort gvkey borrowercompanyid term_loan exposuredate_y exposuredate_q
		
		
		joinby gvkey borrowercompanyid term_loan using ///
		"$path/0 Data/_Processed/temp/_Archive/deals_aux_file.dta", ///
		unmatched(none)

		
		gen exposuredate_y_q = (exposuredate_y-1960)*4 + exposuredate_q -1
		format exposuredate_y_q %tq
		
		keep if facilityenddate >= td("01jan2019")
		
		
		bysort gvkey borrowercompanyid term_loan exposuredate_y exposuredate_q: ///
			egen exposure_outstanding = total(bank_lending_amount* ///
			(dealactivedate_y_q<= exposuredate_y_q & ///
			exposuredate_y_q <= dealactivedate_y_q+maturity/3))
		
		
		bysort gvkey borrowercompanyid term_loan: egen exposure_outstanding_max = ///
			max(exposure_outstanding > 0 & exposure_outstanding != .)
		drop if exposure_outstanding_max == 0
		drop exposure_outstanding_max
		
		gen new_loan_origination = bank_lending_amount if exposuredate_y_q == dealactivedate_y_q
		bysort borrowercompanyid gvkey term_loan exposuredate_y_q: egen total_new_loan_origination = sum(new_loan_origination)
		replace total_new_loan_origination = . if new_loan_origination == .
		
		replace total_new_loan_origination = 0 if total_new_loan_origination == .
		gsort gvkey borrowercompanyid term_loan exposuredate_y exposuredate_q exposure_outstanding -total_new_loan_origination
		duplicates drop gvkey borrowercompanyid term_loan exposuredate_y exposuredate_q exposure_outstanding, force		
		
		
		
		drop LAultimateparentid-othercovenant
		
		
		bysort gvkey borrowercompanyid term_loan: ///
		gen exposure_outstanding_ch = ///
		(exposure_outstanding - ///
		exposure_outstanding[_n-1])/ ///
		(0.5*exposure_outstanding + ///
		0.5*exposure_outstanding[_n-1])
		
		bysort gvkey borrowercompanyid term_loan: ///
		gen exposure_outstanding_ch_log = ///
		log(exposure_outstanding) - ///
		log(exposure_outstanding[_n-1])
		
		compress
		
		save	"$path/0 Data/_Processed/Master_beforecompu_exposures", replace	
		
		
		** Note: last updated 28 March 2023
